package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.Course;
import model.Train;
import model.TrainCourse;
import model.TrainProperty;
import model.TrainType;
import model.TrainWay;
import util.DBUtil;
import util.Page;
import dao.TrainDAO;

public class TrainDAOImpl implements TrainDAO{
	DBUtil dbu = new DBUtil();
	Connection conn = null;
	PreparedStatement pstat = null;
	ResultSet rs = null;
	public boolean addTrain(Train train) {
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql = "insert into train(ID,TRAINNAME,ADDRESS,MONEY,ORGANIZATION,PROPERTYID,TRAINTYPEID,TRAINWAY,MANAGER,STARTTIME,ENDTIME) values(train_id.nextval,?,?,?,?,?,?,?,?,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, train.getTrainname());
			pstat.setString(2, train.getAddress());
			pstat.setDouble(3, train.getMoney());
			pstat.setString(4, train.getOrganization());
			pstat.setInt(5, train.getTrainProperty().getId());
			pstat.setInt(6,train.getTrainType().getId());
			pstat.setInt(7, train.getTrainWay().getId());
			pstat.setInt(8, train.getManager().getId());
			pstat.setDate(9, new java.sql.Date(train.getStarttime().getTime()));
			pstat.setDate(10, new java.sql.Date(train.getEndtime().getTime()));
			int i=pstat.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		
		return flag;
	}
	public int getMaxId()
	{
		int count=0;
		try{
		conn = dbu.getConnection();
		String sql = "select max(id) from train";
		pstat = conn.prepareStatement(sql);
		rs = pstat.executeQuery();
		while (rs.next()) {
			count=rs.getInt(1);
		}
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return count;
	}
	public boolean deleteById(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql="delete from TRAINCOURSE where TRAINID=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			pstat.executeUpdate();
			sql="delete from train where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			pstat.executeUpdate();
			flag=true;
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Page<Train> getAll(int pageNo, int pageSize) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Train> getByOrganization(int pageNo, int pageSize,
			String organization) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Train> getByProperty(int pageNo, int pageSize,
			TrainProperty trainProperty) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train where PROPERTYID=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, trainProperty.getId());
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train where PROPERTYID=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, trainProperty.getId());
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Train> getByTime(int pageNo, int pageSize, Date starttime,
			Date endtime) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train where STARTTIME>=? and ENDTIME<=?";
			pstat=conn.prepareStatement(sql);
			pstat.setDate(1, new java.sql.Date(starttime.getTime()));
			pstat.setDate(2, new java.sql.Date(endtime.getTime()));
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train where STARTTIME>=? and ENDTIME<=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDate(1, new java.sql.Date(starttime.getTime()));
			pstat.setDate(2, new java.sql.Date(endtime.getTime()));
			pstat.setInt(3, page.getPageNo()*pageSize);
			pstat.setInt(4,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Train> getByTrainName(int pageNo, int pageSize, String trainname) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train where TRAINNAME like'%"+trainname+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train where TRAINNAME like'%"+trainname+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Train> getByTrainType(int pageNo, int pageSize,
			TrainType trainType) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train where TRAINTYPEID=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, trainType.getId());
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train where TRAINTYPEID=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, trainType.getId());
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Train> getByTrainWay(int pageNo, int pageSize, TrainWay trainWay) {
		Page<Train> page = new Page<Train>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Train> list = new ArrayList<Train>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from train where TRAINWAY=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, trainWay.getId());
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from train where TRAINWAY=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, trainWay.getId());
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Train  t = new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
				list.add(t);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean updateTrain(Train train) {
		boolean flag=true;
		try{
			conn=dbu.getConnection();
			String sql="update train set TRAINNAME=?,ADDRESS=?,MONEY=?,ORGANIZATION=?,PROPERTYID=?" +
					",TRAINTYPEID=?,TRAINWAY=?,STARTTIME=?,ENDTIME=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, train.getTrainname());
			pstat.setString(2, train.getAddress());
			pstat.setDouble(3, train.getMoney());
			pstat.setString(4, train.getOrganization());
			pstat.setInt(5, train.getTrainProperty().getId());
			pstat.setInt(6, train.getTrainType().getId());
			pstat.setInt(7, train.getTrainType().getId());
			pstat.setDate(8, new java.sql.Date(train.getStarttime().getTime()));
			pstat.setDate(9, new java.sql.Date(train.getEndtime().getTime()));
			pstat.setInt(10, train.getId());
			pstat.executeUpdate();
		}catch(Exception e)
		{
			flag=false;
			e.printStackTrace();
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}
	public Train getById(int id) {
		Train t=null;
		try
		{
			conn=dbu.getConnection();
			String sql="select * from train where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while(rs.next())
			{
				t=new Train();
				t.setId(rs.getInt("ID"));
				t.setTrainname(rs.getString("TRAINNAME"));
				t.setAddress(rs.getString("ADDRESS"));
				t.setMoney(rs.getDouble("MONEY"));
				t.setOrganization(rs.getString("ORGANIZATION"));
				t.setTrainProperty(new TrainPropertyDAOImpl().getById(rs.getInt("PROPERTYID")));
				t.setTrainType(new TrainTypeDAOImpl().getById(rs.getInt("TRAINTYPEID")));
				t.setTrainWay(new TrainWayDAOImpl().getById(rs.getInt("TRAINWAY")));
				t.setManager(new EmpDAOImpl().getEmpById(rs.getInt("MANAGER")));
				t.setStarttime(rs.getDate("STARTTIME"));
				t.setEndtime(rs.getDate("ENDTIME"));
				t.setTrainCourse(new TrainCourseDAOImpl().getByTrainId(rs.getInt("ID")));
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return t;
	}

}
